package cn.com.shopec.erp.stock.dao.impl;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import cn.com.shopec.erp.common.constant.SZSCMConstant;
import cn.com.shopec.erp.common.orm.impl.HibernateEntityDao;
import cn.com.shopec.erp.common.utils.StringUtil;
import cn.com.shopec.erp.stock.commo.ObjectUtil;
import cn.com.shopec.erp.stock.dao.IProductBarCodeDao;
import cn.com.shopec.erp.stock.entity.ProductBarCode;
import cn.com.shopec.erp.stock.vo.ProductBarCodeVo;

@Repository
public class ProductBarCodeDaoImpl extends HibernateEntityDao<ProductBarCode> implements IProductBarCodeDao {

	@Override
	public int getProductsCount(ProductBarCodeVo productBarCodeVo) {
		DetachedCriteria detachedCriteria = createCriteria();
		if (ObjectUtil.isNotNull(productBarCodeVo.getStockOutTypeNo())) {
			detachedCriteria.add(Restrictions.eq("stockOutTypeNo", productBarCodeVo.getStockOutTypeNo()));
		} else if (!StringUtil.isEmpty(productBarCodeVo.getStockInProductId())) {
			detachedCriteria.add(Restrictions.eq("stockInProductId", productBarCodeVo.getStockInProductId()));
		} else {
			detachedCriteria.add(Restrictions.eq("stockOutProductId", productBarCodeVo.getStockOutProductId()));
		}
		detachedCriteria.add(Restrictions.eq("deletedFlag", SZSCMConstant.DELETE_FLAG_NO));
		return getCountByCriteria(detachedCriteria);
	}

	@SuppressWarnings({ "unchecked" })
	@Override
	public List<ProductBarCodeVo> getProductsList(ProductBarCodeVo productBarCode, int pageCount, int pageSize) {
		String sql = "SELECT TPB.ID AS id," + "TPB.CODE_NO AS codeNo," + "TPB.STOCKIN_NO AS stockInNo,"
				+ "TPB.STOCKIN_TYPE AS stockInType," + "TPB.STOCKIN_TYPE_NO AS stockInTypeNo,"
				+ "TPB.STOCKIN_TYPE_NO AS stockInTypeNo," + "TPB.SUP_NO AS supNo," + "TPB.PROD_NO AS prodNo,"
				+ "TPB.COMMO_NO AS commoNo," + "TPB.PUR_NO AS purNo,"
				+ "CAST(TPB.PORD_QUANTITY AS CHAR) AS prodQuantity," + "TSI.SUP_NAME AS supName,"
				+ "C.COMMO_NAME AS commoName," + "GROUP_CONCAT(SVO.OPTION_NAME) AS specification"
				+ " FROM TBL_PRODUCT_BARCODE TPB" + " LEFT JOIN TBL_SUPPLIER_INFO TSI ON TSI.CODE = TPB.SUP_NO"
				+ " LEFT JOIN TBL_PRODUCT PROD ON PROD.PROD_NO = TPB.PROD_NO"
				+ " LEFT JOIN TBL_COMMODITY C ON C.COMMO_NO = TPB.COMMO_NO"
				+ " LEFT OUTER JOIN TBL_PROD_SPEC_VALUE PSV ON PSV.PROD_NO = TPB.PROD_NO"
				+ " LEFT OUTER JOIN TBL_SPEC_VAL_OPTION SVO ON SVO.OPTION_NO = PSV.VAL_OPTION_NO "
				+ " WHERE TPB.DELETED_FLAG = 1 ";
		String value = null;
		if (ObjectUtil.isNotNull(productBarCode.getStockOutTypeNo())) {
			sql = sql + " AND TPB.STOCKOUT_TYPE_NO = ?";
			value = productBarCode.getStockOutTypeNo();
		} else if (!StringUtil.isEmpty(productBarCode.getStockInProductId())) {
			sql = sql + " AND TPB.STOCKIN_PRODUCT_ID = ?";
			value = productBarCode.getStockInProductId();
		} else {
			sql = sql + " AND TPB.STOCKOUT_PRODUCT_ID = ?";
			value = productBarCode.getStockOutProductId();
		}
		sql += " GROUP BY TPB.ID";

		Query query = getSession().createSQLQuery(sql).setParameter(0, value).setFirstResult(pageCount)
				.setMaxResults(pageSize);
		query.setResultTransformer(Transformers.aliasToBean(ProductBarCodeVo.class));
		List<ProductBarCodeVo> stockOutProductVos = query.list();
		return stockOutProductVos;
	}

	@Override
	public void deleteBarCodeForProductId(String stockInProductId) {
		String hql = "delete from ProductBarCode as p where p.stockInProductId = ?";
		getSession().createQuery(hql).setString(0, stockInProductId).executeUpdate();
	}

	@Override
	public ProductBarCode getNoStockOutBarCode(String codeNo) {
		String hql = "from ProductBarCode as p where p.codeNo = ? and p.deletedFlag = 1 and p.effectiveState = 1 order by createTime desc";
		List<?> querys = getSession().createQuery(hql).setString(0, codeNo).list();
		if (querys != null && querys.size() > 0) {
			return (ProductBarCode) querys.get(0);
		}
		return null;
	}

	@Override
	public void setBarCodeStockOut(ProductBarCode productBarCodess, List<ProductBarCode> productBarCodes) {
		StringBuilder builder = new StringBuilder(
				"update ProductBarCode p set p.stockOutNo = ?,p.stockOutProductId = ? ,p.stockOutType = ? ,p.stockOutTypeNo = ? where p.id in (");
		for (ProductBarCode productBarCodeTemp : productBarCodes) {
			builder.append("'");
			builder.append(productBarCodeTemp.getId());
			builder.append("',");
		}
		String hql = builder.toString();
		hql = builder.substring(0, hql.length() - 1) + ") and p.deletedFlag = 1 and p.effectiveState = 1";
		this.getSession().createQuery(hql).setString(0, productBarCodess.getStockOutNo())
				.setString(1, productBarCodess.getStockOutProductId()).setInteger(2, productBarCodess.getStockOutType())
				.setString(3, productBarCodess.getStockOutTypeNo()).executeUpdate();
	}

	@Override
	public void cancelBarCodeStateStockOut(String stockOutProductId) {
		String hql = "update ProductBarCode p set p.stockOutNo = null,p.stockOutProductId = null,p.stockOutType = null,p.stockOutTypeNo = null where p.stockOutProductId = ?  and p.deletedFlag = 1 and p.effectiveState = 1";
		getSession().createQuery(hql).setString(0, stockOutProductId).executeUpdate();
	}

	@Override
	public void setBarCodeEffectiveState(String id, String codeNo, int state) {
		String hql = "update ProductBarCode p set p.effectiveState = ? where p.codeNo = ? and id != ? and p.deletedFlag = 1";
		getSession().createQuery(hql).setInteger(0, state).setString(1, codeNo).setString(2, id).executeUpdate();
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<Object[]> queryForStockOut(String stockOutProductId) {
		String sql = "SELECT pur_no,COUNT(1) FROM tbl_product_barcode WHERE STOCKOUT_PRODUCT_ID = ? and deleted_Flag = 1 and effective_State = 1 GROUP BY pur_no";
		Query query = getSession().createSQLQuery(sql).setString(0, stockOutProductId);
		return query.list();
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<Object[]> queryForStockIn(String stockInProductId) {
		String sql = "SELECT pur_no,COUNT(1) FROM tbl_product_barcode WHERE STOCKIN_PRODUCT_ID = ? and deleted_Flag = 1 and effective_State = 1 GROUP BY pur_no";
		Query query = getSession().createSQLQuery(sql).setString(0, stockInProductId);
		return query.list();
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<Object[]> queryForStockTransfer(String transferNo, String prodNo) {
		String sql = "SELECT pur_no,COUNT(1) FROM tbl_product_barcode WHERE STOCKIN_TYPE_NO = ? and PROD_NO = ? and deleted_Flag = 1 GROUP BY pur_no";
		Query query = getSession().createSQLQuery(sql).setString(0, prodNo).setString(1, transferNo);
		return query.list();
	}

	@Override
	public void updateProductBarCodeForTransfer(String stockInTypeNo, String whNo, String whName) {
		String hql = "update ProductBarCode p set p.whNo = ?,p.whName = ? where p.stockOutTypeNo = ?";
		getSession().createQuery(hql).setString(0, whNo).setString(1, whName).setString(2, stockInTypeNo).executeUpdate();
	}

}
